Memory leak with Excel

Hi have problem of great memory leak with Excel.

How can i dispose the OleAutoObj?

At the moment for each of the object after i used it i set it to null

I also delete each OleAutoArgs i use.

Is there other things  i can do?


bungle77 - Tue May 21 05:13:33 EDT 2013

Re: Memory leak with Excel
llandale - Tue May 21 11:01:54 EDT 2013

  • oleCloseAutoObject(oleExcel)

I doubt failure will cause much memory leak unless you have 10K such undeleted handles.  And if you do you should get performance leak, not memory leak.

-Louie

Re: Memory leak with Excel
bungle77 - Tue May 21 11:04:32 EDT 2013

llandale - Tue May 21 11:01:54 EDT 2013

  • oleCloseAutoObject(oleExcel)

I doubt failure will cause much memory leak unless you have 10K such undeleted handles.  And if you do you should get performance leak, not memory leak.

-Louie

I can use oleCloseAutoObject for each OleAutoObj i declare?

Re: Memory leak with Excel
llandale - Tue May 21 13:34:03 EDT 2013

bungle77 - Tue May 21 11:04:32 EDT 2013

I can use oleCloseAutoObject for each OleAutoObj i declare?

I don't think "I declare" is correct.  You should use oleCloseAutoObject for every one you create but no longer need.  For example, if you loop through all the "Sheet"s you probably want to oleCloseAutoObject(oaoSheet) inside the loop.  Think of them like "Buffer" variables; match the "create" with a "delete".

-Louie

Re: Memory leak with Excel
bungle77 - Wed May 22 04:30:46 EDT 2013

llandale - Tue May 21 13:34:03 EDT 2013

I don't think "I declare" is correct.  You should use oleCloseAutoObject for every one you create but no longer need.  For example, if you loop through all the "Sheet"s you probably want to oleCloseAutoObject(oaoSheet) inside the loop.  Think of them like "Buffer" variables; match the "create" with a "delete".

-Louie

so it is what i already did... If i just declare an OleAutoObj i don't need to do anything.

I attach an extract of my excel library, if i call the function InsertCell many time i have a huge memory leak. Do you have idea how to optimize it?


Attachments

provaExcelforum.dxl

Re: Memory leak with Excel
llandale - Wed May 22 11:47:20 EDT 2013

bungle77 - Wed May 22 04:30:46 EDT 2013

so it is what i already did... If i just declare an OleAutoObj i don't need to do anything.

I attach an extract of my excel library, if i call the function InsertCell many time i have a huge memory leak. Do you have idea how to optimize it?

Preamble: my company changed my Employee ID, and re-used the old ID for some other employee.  Changing around these IDs was confusing, but affected neither me nor the other employee.  Erasing my ID did not erase me.

We are not communicating, sorry.  Hopefully someone else will say this more gracefully than I can, but let's not confuse a "variable" (my employee ID) with an "Allocated Unit" (me).  The variable simply holds a pointer, the address, of the Unit.  Changing the value of the variable has no effect on the unit (other than often denying any future access to it).  The following will leave a "Buffer" allocated unit which can no longer be referenced:

  • Buffer buf = create()  // this 'create' allocates a Buffer unit and returns it's address
  • buf = null   // this erases the variable, but does not remove the Buffer allocated unit

Declaring a variable that can point to allocated units doesn't waste anything.  Setting it to null doesn't recapture anything.

It is a little more confusing with these "OLE" things, since you allocate a unit via reference and don't need any "create".  Declaring an OleAutoObj doesn't leak.  Using one creates creates some "allocation unit" and this unit should be de-allocated with oleCloseAutoObject.  Setting the variable to null does NOT de-allocate the unit.

I see the following places you should oleCloseAutoObject the object:

  • Line #38 objOleColum
  • Line #55 objOleInterior
  • Line #119 objOleFont
  • Line #181 objCell

But, it doesn't feel like this is your real "memory leak" problem

-Louie

I suspect you are using global variables in a failed attempt to reduce your leak.  At best that is confusing.  I note though that in function "writeCellValue()" line #150 you "return objOleCell" which is a global variable.  Do you see that if you call this twice, you have erased the variable of the 1st call?  Also, if you oleCloseAutoObj for the 1st call, you have deleted the 2nd.  Returning a global is begging for future problems that is practically impossible to track down.

Re: Memory leak with Excel
Richard_Good - Wed May 22 14:12:06 EDT 2013

bungle77 - Wed May 22 04:30:46 EDT 2013

so it is what i already did... If i just declare an OleAutoObj i don't need to do anything.

I attach an extract of my excel library, if i call the function InsertCell many time i have a huge memory leak. Do you have idea how to optimize it?

My advice would be to solve your problems another way. I inherited a few "control excel with dxl" type files at a clients. These can almost always be replaced by a combination of a csv file / text output and an XLT file with macros in it. The result is a far simpler system. You can read the csv file into the speadsheet using the macro, pass arguments to the macro from the dxl and process an ini file from the excel file if you have complicated proessing to do. You can also have any flashy charts and summary sheets/ formatting in the XLT already and simply update them with the new ranges using the excel macro.

Re: Memory leak with Excel
bungle77 - Thu May 23 11:00:20 EDT 2013

llandale - Wed May 22 11:47:20 EDT 2013

Preamble: my company changed my Employee ID, and re-used the old ID for some other employee.  Changing around these IDs was confusing, but affected neither me nor the other employee.  Erasing my ID did not erase me.

We are not communicating, sorry.  Hopefully someone else will say this more gracefully than I can, but let's not confuse a "variable" (my employee ID) with an "Allocated Unit" (me).  The variable simply holds a pointer, the address, of the Unit.  Changing the value of the variable has no effect on the unit (other than often denying any future access to it).  The following will leave a "Buffer" allocated unit which can no longer be referenced:

  • Buffer buf = create()  // this 'create' allocates a Buffer unit and returns it's address
  • buf = null   // this erases the variable, but does not remove the Buffer allocated unit

Declaring a variable that can point to allocated units doesn't waste anything.  Setting it to null doesn't recapture anything.

It is a little more confusing with these "OLE" things, since you allocate a unit via reference and don't need any "create".  Declaring an OleAutoObj doesn't leak.  Using one creates creates some "allocation unit" and this unit should be de-allocated with oleCloseAutoObject.  Setting the variable to null does NOT de-allocate the unit.

I see the following places you should oleCloseAutoObject the object:

  • Line #38 objOleColum
  • Line #55 objOleInterior
  • Line #119 objOleFont
  • Line #181 objCell

But, it doesn't feel like this is your real "memory leak" problem

-Louie

I suspect you are using global variables in a failed attempt to reduce your leak.  At best that is confusing.  I note though that in function "writeCellValue()" line #150 you "return objOleCell" which is a global variable.  Do you see that if you call this twice, you have erased the variable of the 1st call?  Also, if you oleCloseAutoObj for the 1st call, you have deleted the 2nd.  Returning a global is begging for future problems that is practically impossible to track down.

thanks a Louie,

you undestood well. I made many attempt to reduce the leak. Set a OleAutoObj to null i could understand it was useless, i just saw somewhere doing it. You also understood i try to use global var to reduce the leak.

Thanks a lot for your tips

Thanks also richard, but at the moment i can't use a CSV file.

Next time anyway i will use VBA instead of DXL and i will access to doors with the runstr command

 

Re: Memory leak with Excel
David_G_Bond - Mon Jun 03 10:24:00 EDT 2013

Richard_Good - Wed May 22 14:12:06 EDT 2013

My advice would be to solve your problems another way. I inherited a few "control excel with dxl" type files at a clients. These can almost always be replaced by a combination of a csv file / text output and an XLT file with macros in it. The result is a far simpler system. You can read the csv file into the speadsheet using the macro, pass arguments to the macro from the dxl and process an ini file from the excel file if you have complicated proessing to do. You can also have any flashy charts and summary sheets/ formatting in the XLT already and simply update them with the new ranges using the excel macro.

An alternative is to export as xml using the Microsoft Excel model. As an example, save a simple excel file as XML Spreadsheet 2003 and then mimic the output. At my company I've created an include file to help with this. Exporting data is much more efficient. Data must, however, be sequential (no inserting cells in the middle of the spreadsheet, for example) and OLEs and charts are not supported.

XML exports support quite a lot of formatting and allows setting fonts, borders, auto filters, column widths and many other options. One strategy we employ is to export as xml, open it up in Excel, make further modifications, and then save as an Excel file. This approach has improved export performance considerably. If there is interest, I'll write more about it.

- David Bond